SET NOCOUNT ON
USE Northgale
go
CREATE OR ALTER PROCEDURE dynamic_search_2
       @orderid     int          = NULL,
       @status      char(1)      = NULL,
       @fromdate    date         = NULL,
       @todate      date         = NULL,
       @custid      nchar(5)     = NULL,
       @custname    nvarchar(40) = NULL,
       @city        nvarchar(25) = NULL,
       @region      nvarchar(15) = NULL,
       @prodid      int          = NULL,
       @prodname    nvarchar(40) = NULL,
       @debug       bit          = 0 AS

DECLARE @sql        nvarchar(MAX),
        @paramlist  nvarchar(4000),
        @nl         char(2) = char(13) + char(10),
        @datediff   int

SELECT @sql =
    'SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,
            c.CustomerID, c.CustomerName, c.Address, c.City,
            c.Region, c.PostalCode, c.Country, c.Phone,
            p.ProductID, p.ProductName, p.UnitsInStock,
            p.UnitsOnOrder, o.EmployeeID
     FROM   dbo.Orders o
     JOIN   dbo.[Order Details] od ON o.OrderID = od.OrderID
     JOIN   dbo.Customers c ON o.CustomerID = c.CustomerID
     JOIN   dbo.Products p ON p.ProductID = od.ProductID
     WHERE  1 = 1' + @nl

IF @orderid IS NOT NULL
   SELECT @sql += ' AND o.OrderID = @orderid' + @nl

IF @status IS NOT NULL
   SELECT @sql += ' AND o.Status = @status' +
                  CASE WHEN @status <> 'C'
                       THEN ' AND o.Status <> ''C'''
                       ELSE ''
                  END + @nl

IF @fromdate IS NOT NULL AND @todate IS NOT NULL
BEGIN
   SELECT @datediff = datediff(DAY, @fromdate, @todate)
   SELECT @sql += CASE WHEN @datediff =  0  THEN '-- Single day'
                       WHEN @datediff <= 7  THEN '-- A week'
                       WHEN @datediff <= 30 THEN '-- A month'
                       ELSE                      '-- More than a month'
                  END + @nl
END

IF @fromdate IS NOT NULL
   SELECT @sql += ' AND o.OrderDate >= @fromdate' + @nl

IF @todate IS NOT NULL
   SELECT @sql += ' AND o.OrderDate <= @todate'  + @nl

IF @custid IS NOT NULL
   SELECT @sql += ' AND o.CustomerID = @custid' + @nl

IF @custname IS NOT NULL
   SELECT @sql += ' AND c.CustomerName LIKE @custname + ''%''' + @nl

-- Handle one city specially.
IF @city IS NOT NULL
   SELECT @sql += ' AND c.City = ' + 
                  CASE @city WHEN N'London' THEN N'N''London''' 
                             ELSE '@city' 
                  END + @nl

IF @region IS NOT NULL
   SELECT @sql += ' AND c.Region = @region' + @nl

IF @prodid IS NOT NULL
   SELECT @sql += ' AND od.ProductID = @prodid' + @nl

IF @prodname IS NOT NULL
   SELECT @sql += ' AND p.ProductName LIKE @prodname + ''%''' + @nl

SELECT @sql += ' ORDER BY o.OrderID' + @nl

IF @debug = 1
   PRINT @sql

SELECT @paramlist = '@orderid     int,
                     @status      char(1),
                     @fromdate    date,
                     @todate      date,
                     @custid      nchar(5),
                     @custname    nvarchar(40),
                     @region      nvarchar(15),
                     @prodid      int,
                     @city        nvarchar(15),
                     @prodname    nvarchar(40)'

EXEC sp_executesql @sql, @paramlist,
                   @orderid, @status, @fromdate, @todate,
                   @custid, @custname, @region,
                   @prodid, @city, @prodname
go
-- Check the query plan.
EXEC dynamic_search_2 @status = 'E', @debug = 1

-- Look at the genererad SQL.
EXEC dynamic_search_2 @custid = 'ERNTC',
                      @fromdate = '19980218', @todate = '19980218',
                      @debug = 1
EXEC dynamic_search_2 @custid = 'BOLSR',
                      @fromdate = '19980101', @todate = '19981231',
                      @debug = 1

-- Searches on cities.
EXEC dynamic_search_2 @prodid = 76, @city = N'London', @debug = 1
EXEC dynamic_search_2 @prodid = 76, @city = N'Paris', @debug = 1

